package com.dz.tool.Service.Impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.dz.tool.Dao.Master.MasterSQLServerMapper;
import com.dz.tool.Dao.Slave.SlaveMysqlMapper;
import com.dz.tool.Service.SQLServerServer;
import com.dz.tool.Utils.ConnectionUtils;
import com.dz.tool.Utils.Excel.ExcelUtil;
import com.dz.tool.Utils.Txt.TXTUtils;
import com.dz.tool.Vo.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CountDownLatch;

@Service
public class SQLServerServerImpl implements SQLServerServer {

    @Autowired
    MasterSQLServerMapper masterSQLServerMapper;
    @Autowired
    SlaveMysqlMapper slaveMysqlMapper;
    @Autowired
    OracleServerImpl oracleServerImpl;

    @Value("${spring.datasource.dynamic.datasource.db1.url}")
    private String MasterUrl;
    @Value("${spring.datasource.dynamic.datasource.db1.username}")
    private String MasterUsername;
    @Value("${spring.datasource.dynamic.datasource.db1.password}")
    private String MasterPassword;


    @Value("${spring.datasource.dynamic.datasource.db2.url}")
    private String SlaveUrl;
    @Value("${spring.datasource.dynamic.datasource.db2.username}")
    private String SlaveUsername;
    @Value("${spring.datasource.dynamic.datasource.db2.password}")
    private String SlavePassword;

    ///////////////////////////////////////////////////////////////////////////
    // todo SQLServer 针对数据库 生成字段Excel文档
    ///////////////////////////////////////////////////////////////////////////
    @Override
    public void SQLServerToExcel(HttpServletResponse response, List<String> tableNames) {
        if (tableNames.size()<1) {
            // 获取所有表名
            tableNames = masterSQLServerMapper.selectTableNameAll();
        }

        List<Map<String,Object>> endList = new ArrayList<>();

        // 初始表头
        Map<String,Object> startMap = new HashMap<>();
        startMap.put("tableName","表列表");
        // 表头数据
        List<Object> headOne = Arrays.asList("表名");
        // 将数据汇总
        List<List<Object>> startSheetDataList = new ArrayList<>();
        startSheetDataList.add(headOne);
        // 数据
        if (tableNames.size()>0) {
            for (String name : tableNames) {
                List<Object> mysqlFieldsList = new ArrayList<>();
                mysqlFieldsList.add(name);
                startSheetDataList.add(mysqlFieldsList);
            }
        }
        startMap.put("sheetDataList",startSheetDataList);
        endList.add(startMap);

        // 表明细
        for (String tableName : tableNames) {
            Map<String,Object> map = new HashMap<>();
            map.put("tableName",tableName);
            List<SQLServerField> sqlServerFields = masterSQLServerMapper.selectTableNameOnCNAndTP(tableName);
            // 表头数据
            List<Object> head = Arrays.asList("表名","列名","数据类型","长度","注释");
            // 将数据汇总
            List<List<Object>> sheetDataList = new ArrayList<>();
            sheetDataList.add(head);
            // 数据
            if (sqlServerFields.size()>0) {
                for (SQLServerField mysqlField : sqlServerFields) {
                    List<Object> mysqlFieldsList = new ArrayList<>();
                    mysqlFieldsList.add(tableName);
                    mysqlFieldsList.add(mysqlField.getFIELD_NAME());
                    mysqlFieldsList.add(mysqlField.getFIELD_TYPE());
                    mysqlFieldsList.add(mysqlField.getFIELD_LENGTH()+","+mysqlField.getDECIMAL_PLACES());
                    mysqlFieldsList.add(mysqlField.getFIELD_DESCRIPTION());
                    sheetDataList.add(mysqlFieldsList);
                }
            }
            map.put("sheetDataList",sheetDataList);
            endList.add(map);
        }

        // 导出数据
        ExcelUtil.exportAll(response,null,"dataBaseToolExcel", endList);
    }


    ///////////////////////////////////////////////////////////////////////////
    // todo SQLServer 数据迁移到 MYSQL
    ///////////////////////////////////////////////////////////////////////////
    @Override
    public void SQLServerToMysql(List<String> tableList, boolean dataBool, boolean dataBaseCreat) throws Exception {

        SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        if (tableList.size()<1) {
            tableList = masterSQLServerMapper.selectTableNameAll();
        }
        if(tableList.size()<1){
            System.out.println("没有获取到需要迁移的数据表！！");
            return;
        }
        System.out.println("数据表数量："+tableList.size());

        List<SQLServerTable> extable = new ArrayList<>();

        int count = tableList.size();
        int number = 1;

        for (String tableName : tableList) {

            System.out.println("=============================");
            System.out.println("开始时间："+ sdf.format(new Date()));
            System.out.println(number++ + "/" + count + " 数据表名：" + tableName + " 开始");

            SQLServerTable sqlServerTable = new SQLServerTable();
            sqlServerTable.setTableName(tableName);
            sqlServerTable.setTableNameSlave(tableName);
            List<SQLServerField> sqlServerFields = masterSQLServerMapper.selectTableNameOnCNAndTP(tableName);
            sqlServerTable.setFieldList(sqlServerFields);
            System.out.println("字段数量："+sqlServerFields.size());

            if (sqlServerTable.getFieldList().size()<1) {
                sqlServerTable.setExMessage("此数据表无字段或者当前用户下没有这个表，无法迁移！！");
                extable.add(sqlServerTable);
                continue;
            }

            // 创建SQL
            creatTableSql(sqlServerTable);

            if (StringUtils.isNotBlank(sqlServerTable.getExMessage())) {
                System.out.println(sqlServerTable.getExMessage());
                extable.add(sqlServerTable);
                continue;
            }


            boolean InTable = false;
            String selectTable = slaveMysqlMapper.selectTable(sqlServerTable.getTableName());
            if (StringUtils.isNotBlank(selectTable)) {
                InTable = true;
                System.out.println("数据表存在，跳过创建！！");
            }
            else {
                InTable = false;
                System.out.println("数据表不存在，按照原始库开始创建表！！");
            }

            if (!InTable) {
                // 执行创建表
                try {
                    slaveMysqlMapper.creatMysql(sqlServerTable.getTableSql());
                }catch (Exception e){
                    sqlServerTable.setExMessage("创建失败 ：" + sqlServerTable.getTableSql());
                    System.out.println(sqlServerTable.getExMessage());
                    extable.add(sqlServerTable);
                    continue;
                }

                // 创建主键字段
                System.out.println("主键字段数量为：" + sqlServerTable.getFieldKeyList().size());
                if (sqlServerTable.getFieldKeyList().size()>0) {
                    String FieldKeySql = "alter table " + sqlServerTable.getTableName() + " add primary key(";
                    int fieldKeyn = 0;
                    for (String fieldKey : sqlServerTable.getFieldKeyList()) {
                        if (fieldKeyn!=0) {
                            FieldKeySql = FieldKeySql + ",";
                        }
                        FieldKeySql = FieldKeySql + fieldKey;
                        ++fieldKeyn;
                    }
                    FieldKeySql = FieldKeySql + ")";
                    System.out.println(FieldKeySql);
                    try {
                        slaveMysqlMapper.creatMysql(FieldKeySql);
                    }catch (Exception e){
                        sqlServerTable.setExMessage("创建主键失败 ：" + FieldKeySql);
                        extable.add(sqlServerTable);
                        continue;
                    }
                    System.out.println("主键创建完成");
                }


                // 创建索引、对比索引
                List<String> selectFiledIndex = masterSQLServerMapper.selectIndex(sqlServerTable.getTableName());
                System.out.println("索引数量为：" + selectFiledIndex.size());
                if (selectFiledIndex!=null) {
                    if (selectFiledIndex.size()>0) {
                        if (selectFiledIndex.size()==1&&selectFiledIndex.get(0)==null) {
                            System.out.println("获取数量异常，是个NULL,跳过！！");
                        }else {
                            String exMessage = "";
                            for (String filedIndex : selectFiledIndex) {
                                try {
                                    if (filedIndex==null || filedIndex.equals("null")) {
                                        System.out.println("NULL索引，跳过！！");
                                        continue;
                                    }
                                    String creatIndexSql = "ALTER TABLE `"+sqlServerTable.getTableName()+"` ADD INDEX `"+filedIndex+"`(";
                                    List<String> indexFiled = masterSQLServerMapper.selectFiledIndex(sqlServerTable.getTableName(), filedIndex);
                                    if (indexFiled.size()>0) {
                                        for (String s : indexFiled) {
                                            if (s==null || s.equals("null")) {
                                                System.out.println("NULL字段，跳过！！");
                                                continue;
                                            }
                                            creatIndexSql = creatIndexSql + "`"+s+"`,";
                                        }
                                        creatIndexSql = creatIndexSql.substring(0,creatIndexSql.length()-1);
                                        creatIndexSql = creatIndexSql + ")";
                                    }
                                    else {
                                        if (StringUtils.isNotBlank(exMessage)) {
                                            exMessage = exMessage + ",";
                                        }
                                        exMessage = exMessage + "数据表：" + sqlServerTable.getTableName() + ", 索引：" + filedIndex + "创建失败！！,没有使用字段！！";
                                    }
                                    System.out.println(creatIndexSql);
                                    slaveMysqlMapper.creatMysql(creatIndexSql);
                                }catch (Exception e){
                                    if (StringUtils.isNotBlank(exMessage)) {
                                        exMessage = exMessage + ",";
                                    }
                                    exMessage = exMessage + "数据表：" + sqlServerTable.getTableName() + ", 索引字段：" + filedIndex + "创建失败！！";
                                }
                            }
                            if (StringUtils.isNotBlank(exMessage)) {
                                sqlServerTable.setExMessage(exMessage);
                                extable.add(sqlServerTable);
                            }
                            System.out.println("索引创建完成");
                        }
                    }
                }
            }

            if (dataBool) {

                int insertNum = 100000; // 开始获取相关表的数据
                int threadCount = 5; // 线程数量

                List<Map<String, Object>> dataList = null;
                String tableNameEnd = tableName;

                Integer selectDataCount = masterSQLServerMapper.selectDataCount(tableNameEnd);
                System.out.println("数据量为：" + selectDataCount);

                if (selectDataCount<=insertNum) {
                    System.out.println("一次性存储");
                    dataList = masterSQLServerMapper.selectData(tableNameEnd);
                    boolean bigData = inseBigData(dataList, sqlServerTable, extable);
                    if (!bigData) {
                        continue;
                    }
                }
                else {
                    System.out.println("批量存储");
                    final String tableNameEndFinal = tableNameEnd;

                    BigDecimal one = new BigDecimal(selectDataCount);
                    BigDecimal two = new BigDecimal(insertNum);
                    BigDecimal divide = one.divide(two, RoundingMode.HALF_UP);
                    divide = divide.add(new BigDecimal(1));
                    System.out.println("单次数量为(可能出现次数比你们算的多1,2次，不会少，原因计算逻辑有问题，懒得改，凑乎用吧，否则自己搞去)：" + divide);
                    divide = divide.divide(new BigDecimal(threadCount), RoundingMode.HALF_UP);
                    divide = divide.add(new BigDecimal(1));
                    System.out.println("除以线程次数一共为(可能出现次数比你们算的多1,2次，不会少，原因计算逻辑有问题，懒得改，凑乎用吧，否则自己搞去)：" + divide);

                    final boolean[] OK = {true,true,true,true,true};
                    final int[] start = {0};
                    final int[] end = {start[0] + insertNum};
                    for (Integer i = 1; i <= Integer.valueOf(String.valueOf(divide.toString())); i++) {

                        CountDownLatch latch = new CountDownLatch(threadCount);

                        Thread thread = new Thread(()->{
                            String sql = "SELECT * FROM ( SELECT ROW_NUMBER ( ) OVER ( ORDER BY "+sqlServerTable.getFieldList().get(0).getFIELD_NAME()+" ) AS id,* FROM "+tableNameEndFinal+" WHERE 1 = 1 ) AS endTable WHERE id BETWEEN "+start[0]+" AND "+end[0];
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            List<Map<String, Object>> dataListFinal = masterSQLServerMapper.creatOracleMap(sql);
                            OK[0] = inseBigData(dataListFinal, sqlServerTable, extable);
                            latch.countDown();
                        });
                        thread.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread2 = new Thread(()->{
                            String sql = "SELECT * FROM ( SELECT ROW_NUMBER ( ) OVER ( ORDER BY "+sqlServerTable.getFieldList().get(0).getFIELD_NAME()+" ) AS id,* FROM "+tableNameEndFinal+" WHERE 1 = 1 ) AS endTable WHERE id BETWEEN "+start[0]+" AND "+end[0];
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            List<Map<String, Object>> dataListFinal = masterSQLServerMapper.creatOracleMap(sql);
                            OK[1] = inseBigData(dataListFinal, sqlServerTable, extable);
                            latch.countDown();
                        });
                        thread2.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread3 = new Thread(()->{
                            String sql = "SELECT * FROM ( SELECT ROW_NUMBER ( ) OVER ( ORDER BY "+sqlServerTable.getFieldList().get(0).getFIELD_NAME()+" ) AS id,* FROM "+tableNameEndFinal+" WHERE 1 = 1 ) AS endTable WHERE id BETWEEN "+start[0]+" AND "+end[0];
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            List<Map<String, Object>> dataListFinal = masterSQLServerMapper.creatOracleMap(sql);
                            OK[2] = inseBigData(dataListFinal, sqlServerTable, extable);
                            latch.countDown();
                        });
                        thread3.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread4 = new Thread(()->{
                            String sql = "SELECT * FROM ( SELECT ROW_NUMBER ( ) OVER ( ORDER BY "+sqlServerTable.getFieldList().get(0).getFIELD_NAME()+" ) AS id,* FROM "+tableNameEndFinal+" WHERE 1 = 1 ) AS endTable WHERE id BETWEEN "+start[0]+" AND "+end[0];
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            List<Map<String, Object>> dataListFinal = masterSQLServerMapper.creatOracleMap(sql);
                            OK[3] = inseBigData(dataListFinal, sqlServerTable, extable);
                            latch.countDown();
                        });
                        thread4.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread5 = new Thread(()->{
                            String sql = "SELECT * FROM ( SELECT ROW_NUMBER ( ) OVER ( ORDER BY "+sqlServerTable.getFieldList().get(0).getFIELD_NAME()+" ) AS id,* FROM "+tableNameEndFinal+" WHERE 1 = 1 ) AS endTable WHERE id BETWEEN "+start[0]+" AND "+end[0];
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            List<Map<String, Object>> dataListFinal = masterSQLServerMapper.creatOracleMap(sql);
                            OK[4] = inseBigData(dataListFinal, sqlServerTable, extable);
                            latch.countDown();
                        });
                        thread5.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        try {
                            // 等待所有线程执行完毕
                            latch.await();
                        } catch (InterruptedException e) {
                            e.printStackTrace();
                        }
                        System.out.println("等待结束");

                        if(!OK[0]||!OK[1]||!OK[2]||!OK[3]||!OK[4]){
                            break;
                        }
                    }
                    if(!OK[0]||!OK[1]||!OK[2]||!OK[3]||!OK[4]){
                        continue;
                    }
                }

                System.out.println("存储完成");

            }

            System.out.println("数据表名：" + tableName + " 结束");
            System.out.println("结束时间："+ sdf.format(new Date()));
            System.out.println("=============================");

        }

        System.out.println("迁移完成，失败数为：" + extable.size());

        if (extable.size()>0) {
            System.out.println("以下失败表名");
            StringBuffer stringBuffer = new StringBuffer();
            for (SQLServerTable s : extable) {
                System.out.println(s.getTableName() + "原因是：" + s.getExMessage());
                stringBuffer.append(s.getTableName() + "原因是：" + s.getExMessage());
            }
            TXTUtils.creatTxt("exception",stringBuffer.toString());
        }

    }

    public void creatTableSql(SQLServerTable sqlServerTable){

        StringBuffer buffer = new StringBuffer();

        buffer.append("create table "+sqlServerTable.getTableName()+"(");

        int y = 0;

        for (SQLServerField sqlServerField : sqlServerTable.getFieldList()) {

            if(y!=0){
                buffer.append(",");
            }
            ++y;

            // 关键字问题
            int FieldTrue = oracleServerImpl.FieldTrue(sqlServerField.getFIELD_NAME());

            if (FieldTrue<1) {
                sqlServerField.setFIELD_NAME("`"+sqlServerField.getFIELD_NAME() + "S` ");
            }

            buffer.append("`"+sqlServerField.getFIELD_NAME() + "` ");

            // 类型问题
            String FieldType = FieldType(sqlServerField);

            if (StringUtils.isBlank(FieldType)) {
                sqlServerTable.setExMessage(sqlServerField.getFIELD_NAME() + "没有对应的数据类型");
                break;
            }

            sqlServerField.setMYSQL_FIELD_TYPE(FieldType);

            buffer.append(FieldType + " ");

            if (sqlServerField.getPRIMARY_KEY()) {

                // 抽出主键
                sqlServerTable.getFieldKeyList().add(sqlServerField.getFIELD_NAME());

            }
            else {

                //非空问题
                String FieldNotBank = FieldNotBank(sqlServerField);

                buffer.append(FieldNotBank + " ");

            }


            if (StringUtils.isNotBlank(sqlServerField.getFIELD_DESCRIPTION())) {
                buffer.append("COMMENT '" + oracleServerImpl.specialSymbol(sqlServerField.getFIELD_DESCRIPTION()) + "'");
            }

        }

        buffer.append(")ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin");

        sqlServerTable.setTableSql(buffer.toString());

    }

    // Mysql字段类型对比
    public String filedToMysql(SQLServerField sqlServerField,String name){

        // 存储数据过滤
        name = oracleServerImpl.specialSymbol(name);

        String data_type = sqlServerField.getMYSQL_FIELD_TYPE();

        if (data_type.equals("text") ||
                data_type.contains("varchar(") ||
                data_type.equals("longblob")  ||
                data_type.equals("char")
        ) {

            if (name.equals("null")) {
                return "''";
            }

            return "'" + name +"'";

        }


        if (data_type.equals("timestamp(6)")) {

            if (name.equals("null")) {
                return "null";
            }

            return "'" + name +"'";

        }


        if (data_type.equals("date") || data_type.equals("datetime(6)")) {

            if (name.equals("null")) {
                return "NULL";
            }

            return "'" + name + "'";
        }


        if (data_type.contains("integer(") ||
                data_type.contains("decimal(")) {

            return name;

        }

        return null;
    }
    public String FieldType(SQLServerField sqlServerField){
        String name = sqlServerField.getFIELD_TYPE();

        if (name.equals("varchar") && sqlServerField.getFIELD_LENGTH().equals("-1")){
            return "longblob";
        }

        if (name.equals("varchar") || name.equals("nvarchar")) {

            if (Long.parseLong(sqlServerField.getFIELD_LENGTH()) >= 4000) {
                return "text";
            }

            return "varchar(" + sqlServerField.getFIELD_LENGTH() +")";
        }

        else if (name.equals("datetime")) {
            return "datetime(6)";
        }

        else if (name.equals("date")) {
            return "date";
        }

        else if (name.equals("int")) {
            return "integer(" + sqlServerField.getFIELD_LENGTH() +")";
        }

        else if (name.equals("float") || name.equals("numeric")) {
            if (Integer.valueOf(sqlServerField.getDECIMAL_PLACES())<1) {
                int result = Integer.valueOf(sqlServerField.getFIELD_LENGTH()) / 3;
                result = result + 1;
                return "decimal("+ sqlServerField.getFIELD_LENGTH() +","+result+")";
            }
            else {
                return "decimal("+ sqlServerField.getFIELD_LENGTH() +","+ sqlServerField.getDECIMAL_PLACES() +")";
            }
        }

        if (name.equals("nchar")) {
            return "char";
        }

        return null;
    }

    public String FieldNotBank(SQLServerField sqlServerField){

        if (!sqlServerField.getALLOW_EMPTY()) {
            return "not null";
        }

        return "";
    }

    // 表数据存储
    public boolean inseBigData(List<Map<String, Object>> dataList,SQLServerTable sqlServerTable,List<SQLServerTable> extable) {

        try{
            sqlServerTable.setDataList(dataList);
        }catch (Exception e){
            sqlServerTable.setExMessage("读取数据失败，此问题出现概率低，目前发现可能性有：1.数据获取代码存在问题。 2.当前表不属于当前用户下。");
            extable.add(sqlServerTable);
            return false;
        }

        System.out.println("数据存储量：" + dataList.size());
        if (dataList.size()>0) {

            System.out.println("开始存储");

            // 形成添加语句
            String creatInsert = creatInsertMysql(sqlServerTable);

            // 执行存储数据
            try {

                slaveMysqlMapper.creatMysql(creatInsert);

            }catch (Exception e){

                System.out.println("存储方法一失败！！！执行方法2号");
                e.printStackTrace();

                try {

                    ConnectionUtils connectionUtils = new ConnectionUtils();
                    connectionUtils.sqlConnection(creatInsert,SlaveUrl,SlaveUsername,SlavePassword);

                }catch (Exception e2){

                    sqlServerTable.setExMessage("数据存储二失败："+e.getMessage()+",insert语句为："+creatInsert+",存储失败，猜测原因：1.语句存在问题。2.数据表并非本程序创建，跟原始表可能存在差异导致");
                    System.out.println(sqlServerTable.getExMessage());
                    extable.add(sqlServerTable);
                    return false;
                }

            }

        }

        return true;

    }

    //4.形成添加语句 （Mysql）
    public String creatInsertMysql(SQLServerTable sqlServerTable){

        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer
                .append("insert into " + sqlServerTable.getTableName() + "(");

        int y = 0;
        for (SQLServerField sqlServerField : sqlServerTable.getFieldList()) {
            if (y!=0) {
                stringBuffer.append(",");
            }
            ++y;
            stringBuffer.append(sqlServerField.getFIELD_NAME());
        }
        y = 0;

        stringBuffer.append(") values ");

        for (Map<String, Object> stringObjectMap : sqlServerTable.getDataList()) {

            if (y!=0) {
                stringBuffer.append(",");
            }
            ++y;

            stringBuffer.append("(");

            int a = 0;
            for (SQLServerField sqlServerField : sqlServerTable.getFieldList()) {
                if (a!=0) {
                    stringBuffer.append(",");
                }
                ++a;
                String filedTo = filedToMysql(sqlServerField, String.valueOf(stringObjectMap.get(sqlServerField.getFIELD_NAME())));
                if (StringUtils.isBlank(filedTo)) {
                    filedTo = "";
                }
                stringBuffer.append(filedTo);
            }

            stringBuffer.append(")");

        }

        return stringBuffer.toString();

    }

}
